﻿using System;
using System.Configuration;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;



/// <summary>
/// class  SqlBooksProvider 
///  Provides access to SqlServer data source
/// </summary>
public class SqlBooksProvider : booksProvider
{
    /// <summary>
    /// retrieves the connection string from the web.config
    /// </summary>
    private string connectionString()
    {
        SmallBusinessDataProvidersSection sec = (ConfigurationManager.GetSection("SmallBusinessDataProviders")) as SmallBusinessDataProvidersSection;
        string connectionStringName = sec.PeopleProviders[sec.PeopleProviderName].Parameters["connectionStringName"];
        return WebConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
    }
    /// <summary>
    /// retrieves all book records
    /// </summary>
    public override List<bookatt> GetAllBooks()
    {
        List<bookatt> booksList = new List<bookatt>();

        // create a connection, invoke stored procedure
        //
        using (SqlConnection con = new SqlConnection(connectionString()))
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("GetBooks", con);
            SqlDataReader r = cmd.ExecuteReader();
            bookatt curr;
            while (r.Read())
            {
                if (r["bookId"] is DBNull || r["bName"] is DBNull)
                    throw new InvalidOperationException(Messages.booksRequiredAttributesMissing);
                curr = new bookatt((string)r["bookId"], (string)r["bName"]);
                curr.BDate = (r["bDate"] is DBNull) ? DateTime.MinValue : (DateTime)r["bDate"];
                curr.BName = (r["bName"] is DBNull) ? String.Empty : (string)r["bName"];
                curr.BImageurl = (r["bImageurl"] is DBNull) ? String.Empty : (string)r["bImageurl"];
                curr.BContent = (r["bContent"] is DBNull) ? string.Empty : (string)r["bContent"];
                curr.BRemarks = (r["bRemarks"] is DBNull) ? string.Empty : (string)r["bRemarks"];
                booksList.Add(curr);
            }
        }
        return booksList;
    }

    public override bookatt GetBooksItem(string booksItemId)
    {
        if (String.IsNullOrEmpty(booksItemId)) return null;

        // connect to the database
        bookatt curr;
        using (SqlConnection con = new SqlConnection(connectionString()))
        {
            con.Open();
            SqlCommand cmd = new SqlCommand("GetBooksItem", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@bookId",SqlDbType.Int);
            cmd.Parameters["@bookId"].Value = booksItemId;

            SqlDataReader r = cmd.ExecuteReader();

            r.Read();

            if (r["bookId"] is DBNull || r["bName"] is DBNull)
                throw new InvalidOperationException(Messages.booksItemRequiredAttributesMissing);

               curr = new bookatt((string)r["bookId"], (string)r["bName"]);
                curr.BDate = (r["bDate"] is DBNull) ? DateTime.MinValue : (DateTime)r["bDate"];
                curr.BName = (r["bName"] is DBNull) ? String.Empty : (string)r["bName"];
                curr.BImageurl = (r["bImageurl"] is DBNull) ? String.Empty : (string)r["bImageurl"];
                curr.BContent = (r["bContent"] is DBNull) ? string.Empty : (string)r["bContent"];
                curr.BRemarks = (r["bRemarks"] is DBNull) ? string.Empty : (string)r["bRemarks"];
          
        }

        return curr;
    }

} // end class
